﻿using SQLEditor.admin.entity;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace SQLEditor.admin.service
{
    public class MySqlHelper
    {

        //连接字符串
        static string strConn = ConfigurationManager.ConnectionStrings["dbconn_mysql"].ToString();



        #region 执行查询，返回DataTable对象-----------------------

        public static DataTable GetTable(string strSQL)
        {
            return GetTable(strSQL, null);
        }
        public static DataTable GetTable(string strSQL, MySqlParameter[] pas)
        {
            return GetTable(strSQL, pas, CommandType.Text);
        }
        /// <summary>
        /// 分页查询数据
        /// </summary>
        /// <param name="table">表或视图名</param>
        /// <param name="columns">列名列表</param>
        /// <param name="pageNo">页号</param>
        /// <param name="pageSize">每页数量</param>
        /// <param name="where">条件</param>
        /// <param name="order">排序列名</param>
        /// <returns>分页模型数据</returns>
        public static PagerModel GetTablePager(string table, string columns="*", int pageNo=1,int pageSize=20, string where = "1=1", string order="id")
        {
            int n = 0;
            MySqlParameter totals = new MySqlParameter("@totalCount", n);
            totals.Direction = ParameterDirection.InputOutput;
            MySqlParameter[] pas = new MySqlParameter[]
            {
                new MySqlParameter("@tableName",table),
                new MySqlParameter("@columns",columns),
                new MySqlParameter("@order",order),
                new MySqlParameter("@pageSize",pageSize),
                new MySqlParameter("@pageIndex",pageNo),
                new MySqlParameter("@where",where),
                totals
            };
            DataTable dt = GetTable("pro_Pager", pas, CommandType.StoredProcedure);
            n = Convert.ToInt32( totals.Value);
            return new PagerModel(n, pageNo, pageSize, dt);
        }
        /// <summary>
        /// 执行查询，返回DataTable对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="pas">参数数组</param>
        /// <param name="cmdtype">Command类型</param>
        /// <returns>DataTable对象</returns>
        public static DataTable GetTable(string strSQL, MySqlParameter[] pas, CommandType cmdtype)
        {
            DataTable dt = new DataTable(); ;
            using (MySqlConnection conn = new MySqlConnection(strConn))
            {
                MySqlDataAdapter da = new MySqlDataAdapter(strSQL, conn);
                da.SelectCommand.CommandType = cmdtype;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }
        #endregion

        public static DataTable GetTable(MySqlConnection conn, MySqlTransaction tr, string strSQL, MySqlParameter[] pas, CommandType cmdtype)
        {
            DataTable dt = new DataTable(); ;
            using (MySqlDataAdapter da = new MySqlDataAdapter(strSQL, conn))
            {
                da.SelectCommand.CommandType = cmdtype;
                da.SelectCommand.Transaction = tr;                
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }


        #region 执行查询，返回DataSet对象-------------------------




        public static DataSet GetDataSet(string strSQL)
        {
            return GetDataSet(strSQL, null);
        }

        public static DataSet GetDataSet(string strSQL, MySqlParameter[] pas)
        {
            return GetDataSet(strSQL, pas, CommandType.Text);
        }
        /// <summary>
        /// 执行查询，返回DataSet对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="pas">参数数组</param>
        /// <param name="cmdtype">Command类型</param>
        /// <returns>DataSet对象</returns>
        public static DataSet GetDataSet(string strSQL, MySqlParameter[] pas, CommandType cmdtype)
        {
            DataSet dt = new DataSet(); ;
            using (MySqlConnection conn = new MySqlConnection(strConn))
            {
                MySqlDataAdapter da = new MySqlDataAdapter(strSQL, conn);
                da.SelectCommand.CommandType = cmdtype;
                if (pas != null)
                {
                    da.SelectCommand.Parameters.AddRange(pas);
                }
                da.Fill(dt);
            }
            return dt;
        }
        #endregion





        #region 执行非查询存储过程和SQL语句-----------------------------




        public static int ExcuteProc(string ProcName)
        {
            return ExcuteSQL(ProcName, null, CommandType.StoredProcedure);
        }

        public static int ExcuteProc(string ProcName, MySqlParameter[] pars)
        {
            return ExcuteSQL(ProcName, pars, CommandType.StoredProcedure);
        }

        public static int ExcuteSQL(string strSQL)
        {
            return ExcuteSQL(strSQL, null);
        }

        public static int ExcuteSQL(string strSQL, MySqlParameter[] paras)
        {
            return ExcuteSQL(strSQL, paras, CommandType.Text);
        }

        /// 执行非查询存储过程和SQL语句
        /// 增、删、改
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <param name="cmdType">Command类型</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteSQL(string strSQL, MySqlParameter[] paras, CommandType cmdType)
        {
            int i = 0;
            using (MySqlConnection conn = new MySqlConnection(strConn))
            {
                MySqlCommand cmd = new MySqlCommand(strSQL, conn);
                cmd.CommandType = cmdType;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                i = cmd.ExecuteNonQuery();
                conn.Close();
            }
            return i;
        }

        public static int ExcuteSQL(MySqlConnection conn, MySqlTransaction tr, string strSQL, MySqlParameter[] paras, CommandType cmdType)
        {
            int i = 0;
            using (MySqlCommand cmd = new MySqlCommand(strSQL, conn))
            {                
                cmd.CommandType = cmdType;
                cmd.Transaction = tr;          
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                i = cmd.ExecuteNonQuery();                
            }
            return i;
        }

        public static int ExcuteSQL(List<string> strSQLs)
        {
            int n = 0;
            using (MySqlConnection conn = new MySqlConnection(strConn))
            {
                conn.Open();
                MySqlTransaction tr = conn.BeginTransaction();
                try
                {                    
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn;
                    cmd.Transaction = tr;
                    cmd.CommandType = CommandType.Text;
                    
                    for (int i = 0; i < strSQLs.Count; i++)
                    {
                        cmd.CommandText = strSQLs[i];
                        int x = cmd.ExecuteNonQuery();
                        if (x > 0) n++;
                    }
                    tr.Commit();
                    //if(n!= strSQLs.Count)
                    //{
                    //    tr.Rollback();
                    //    n = 0;
                    //}
                }
                catch (Exception ex)
                {
                    tr.Rollback();
                    n = 0;
                }
                conn.Close();
            }

            return n;
        }


        #endregion








        #region 执行查询返回第一行，第一列---------------------------------




        public static int ExcuteScalarSQL(string strSQL)
        {
            return ExcuteScalarSQL(strSQL, null);
        }

        public static int ExcuteScalarSQL(string strSQL, MySqlParameter[] paras)
        {
            return ExcuteScalarSQL(strSQL, paras, CommandType.Text);
        }
        public static int ExcuteScalarProc(string strSQL, MySqlParameter[] paras)
        {
            return ExcuteScalarSQL(strSQL, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 执行SQL语句，返回第一行，第一列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>返回影响行数</returns>
        public static int ExcuteScalarSQL(string strSQL, MySqlParameter[] paras, CommandType cmdType)
        {
            int i = 0;
            using (MySqlConnection conn = new MySqlConnection(strConn))
            {
                MySqlCommand cmd = new MySqlCommand(strSQL, conn);
                cmd.CommandType = cmdType;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);
                }
                conn.Open();
                i = Convert.ToInt32(cmd.ExecuteScalar());
                conn.Close();
            }
            return i;

        }


        #endregion









        #region 查询获取单个值------------------------------------




        /// <summary>
        /// 调用不带参数的存储过程获取单个值
        /// </summary>
        /// <param name="ProcName"></param>
        /// <returns></returns>
        public static object GetObjectByProc(string ProcName)
        {
            return GetObjectByProc(ProcName, null);
        }
        /// <summary>
        /// 调用带参数的存储过程获取单个值
        /// </summary>
        /// <param name="ProcName"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetObjectByProc(string ProcName, MySqlParameter[] paras)
        {
            return GetObject(ProcName, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 根据sql语句获取单个值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static object GetObject(string strSQL)
        {
            return GetObject(strSQL, null);
        }
        /// <summary>
        /// 根据sql语句 和 参数数组获取单个值
        /// </summary>
        /// <param name="strSQL"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static object GetObject(string strSQL, MySqlParameter[] paras)
        {
            return GetObject(strSQL, paras, CommandType.Text);
        }

        /// <summary>
        /// 执行SQL语句，返回首行首列
        /// </summary>
        /// <param name="strSQL">要执行的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>返回的首行首列</returns>
        public static object GetObject(string strSQL, MySqlParameter[] paras, CommandType cmdtype)
        {
            object o = null;
            using (MySqlConnection conn = new MySqlConnection(strConn))
            {
                MySqlCommand cmd = new MySqlCommand(strSQL, conn);
                cmd.CommandType = cmdtype;
                if (paras != null)
                {
                    cmd.Parameters.AddRange(paras);

                }

                conn.Open();
                o = cmd.ExecuteScalar();
                conn.Close();
            }
            return o;

        }



        #endregion





        #region 查询获取DataReader------------------------------------

        /// <summary>
        /// 调用不带参数的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <returns>DataReader对象</returns>
        public static MySqlDataReader GetReaderByProc(string procName)
        {
            return GetReaderByProc(procName, null);
        }
        /// <summary>
        /// 调用带有参数的存储过程，返回DataReader对象
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="paras">参数数组</param>
        /// <returns>DataReader对象</returns>
        public static MySqlDataReader GetReaderByProc(string procName, MySqlParameter[] paras)
        {
            return GetReader(procName, paras, CommandType.StoredProcedure);
        }
        /// <summary>
        /// 根据sql语句返回DataReader对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <returns>DataReader对象</returns>
        public static MySqlDataReader GetReader(string strSQL)
        {
            return GetReader(strSQL, null);
        }
        /// <summary>
        /// 根据sql语句和参数返回DataReader对象
        /// </summary>
        /// <param name="strSQL">sql语句</param>
        /// <param name="paras">参数数组</param>
        /// <returns>DataReader对象</returns>
        public static MySqlDataReader GetReader(string strSQL, MySqlParameter[] paras)
        {
            return GetReader(strSQL, paras, CommandType.Text);
        }
        /// <summary>
        /// 查询SQL语句获取DataReader
        /// </summary>
        /// <param name="strSQL">查询的SQL语句</param>
        /// <param name="paras">参数列表，没有参数填入null</param>
        /// <returns>查询到的DataReader（关闭该对象的时候，自动关闭连接）</returns>
        public static MySqlDataReader GetReader(string strSQL, MySqlParameter[] paras, CommandType cmdtype)
        {
            MySqlDataReader sqldr = null;
            MySqlConnection conn = new MySqlConnection(strConn);
            MySqlCommand cmd = new MySqlCommand(strSQL, conn);
            cmd.CommandType = cmdtype;
            if (paras != null)
            {
                cmd.Parameters.AddRange(paras);
            }
            conn.Open();
            //CommandBehavior.CloseConnection的作用是如果关联的DataReader对象关闭，则连接自动关闭
            sqldr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return sqldr;
        }



        #endregion


        


        public string GetSqlInsert<T>(T t)
        {
            Type type = t.GetType();
            PropertyInfo[] properties = type.GetProperties();
            string str = "Insert into " + type.Name + " ( ";
            foreach (var proper in properties)
            {
                if (IsKey(proper))
                {
                    continue;
                }
                else
                {
                    str += proper.Name + ",";
                }
            }
            str = str.Substring(0, str.LastIndexOf(","));
            str += " ) values ( ";
            foreach (var proper in properties)
            {
                if (IsKey(proper))
                {
                    continue;
                }
                else
                {
                    object val = proper.GetValue(t, null);
                    if (val is int || val is float || val is decimal || val is double)
                    {
                        str += proper.GetValue(t, null) + ",";
                    }
                    else
                    {
                        if (val == null)
                        {
                            str += "null,";
                        }
                        else
                        {
                            str += "'" + proper.GetValue(t, null) + "'" + ",";
                        }

                    }
                }
            }
            str = str.Substring(0, str.LastIndexOf(","));
            str += " )";
            return str;
        }
        /// <summary>
        ///判断是否是主键
        /// </summary>
        /// <param name="proper"></param>
        /// <returns></returns>
        private bool IsKey(PropertyInfo proper)
        {
            object[] objs = proper.GetCustomAttributes(typeof(AllTargetsAttribute), true);
            bool f = false;
            foreach (var obj in objs)
            {
                if (obj is AllTargetsAttribute)
                {
                    AllTargetsAttribute alltarget = (AllTargetsAttribute)obj;
                    if (alltarget.IsKey)
                    {
                        f = true;
                        break;
                    }
                }
            }
            return f;
        }
    }
}
